package com.infolab.aims;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.commons.lang.RandomStringUtils;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

import com.infolab.aims.common.CommonUtil;
import com.infolab.aims.common.bean.SearchCriteria;
import com.infolab.aims.domain.Location;
import com.infolab.aims.domain.asset.AssetComponent;
import com.infolab.aims.domain.asset.AssetEquipment;
import com.infolab.aims.domain.asset.AssetEquipmentPK;
import com.infolab.aims.domain.asset.AssetModel;
import com.infolab.aims.domain.asset.AssetTransaction;
import com.infolab.aims.domain.asset.AssetTransactionItem;
import com.infolab.aims.service.intf.AssetModelService;
import com.infolab.aims.service.intf.AssetTransactionService;
import com.infolab.aims.service.intf.LocationService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath*:spring/applicationContext*.xml" })
@TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = false)
@Transactional
public class DataMigration {
	
	
	@Autowired
	private LocationService locationService;
	
	@Autowired
	private AssetTransactionService assetTransactionService;
	
	@Autowired
	private AssetModelService assetModelService;
	
	@PersistenceContext
	private EntityManager em;
	
	Log logger = LogFactory.getLog(getClass());
	
	private List<AssetComponent> assetComponents = new ArrayList<AssetComponent>();
	
	private List<AssetEquipment> assetEquipments = new ArrayList<AssetEquipment>();
	
	private Set<AssetModel> assetModels = new HashSet<AssetModel>();
	
	@Test
	public void createActiveEquipment() throws Exception
	{
		Location location = locationService.getLocation(2); // ICMW
		location = locationService.getLocation(1); // SCML
		
		AssetModel assetModel = new AssetModel(); // CAMUS
		assetModel.setAssetModelId(3);
		for(int i=0;i<10;i++)
		{
			AssetEquipment assetEquipment = new AssetEquipment();
			String serialNo = RandomStringUtils.randomNumeric(10);
			AssetEquipmentPK assetEquipmentPK= new AssetEquipmentPK(assetModel,"11"+serialNo);
			assetEquipment.setAssetEquipmentPK(assetEquipmentPK);
			assetEquipment.setLocation(location);
			assetEquipment.setAssetEquipmentStatus(AssetModel.ASSETSTATUS_ACTIVE);
			assetEquipment.setCompanyCode(GlobalConstant.COMPANY_CODE_COMFORT);
			em.persist(assetEquipment);
		}
	}
	
	@Test
	public void createFaultEquipment() throws Exception
	{
		
	}
	@Test
	public void createScript() throws Exception
	{
		Workbook workbook = Workbook.getWorkbook(new File("data-migration/data-migration.xls"));
		FileWriter fw = new FileWriter("data-migration/migration.sql");
		fw.write("set define off;\r\n");
		Sheet[] sheets = workbook.getSheets();
		for(int i=0;i<sheets.length;i++)
		{
			String tableName = sheets[i].getName();
			System.out.println(tableName);
			Sheet sheet= workbook.getSheet(i);
			
			int colLength = sheet.getColumns();
			int rowLength = sheet.getRows();
			String colName[] = new String[colLength];
			String colNameQuote[] = new String[colLength];
			for(int j=0;j<colLength;j++)
			{
				colName[j]=sheet.getCell(j,0).getContents();
				colNameQuote[j]=sheet.getCell(j,1).getContents();
			}
			
			String rowValue[][] = new String[rowLength][colLength];
			
			String sqlInsert = "INSERT INTO "+tableName+"("+StringUtils.join(colName, ",")+") values ";
			for(int j=2;j<rowLength;j++)
			{
				for(int k=0;k<colLength;k++)
				{
					
					rowValue[j][k]=sheet.getCell(k,j).getContents();
					
					if(StringUtils.equalsIgnoreCase(colNameQuote[k], "Y"))
						rowValue[j][k]="'"+StringEscapeUtils.escapeSql(rowValue[j][k])+"'";
					//System.out.println(j+" "+rowValue[j][k]);
				}
				fw.write(sqlInsert+"("+StringUtils.join(rowValue[j],",")+");\r\n");		
			}
		}
		fw.close();
		workbook.close();
	}
	
	@Test
	public void testDriver() throws Exception
	{
		try
		{
		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin@localhost:1521:XE");
		}catch(Exception e)
		{
			logger.error(CommonUtil.getStackTrace(e));
		}
	}
	
	@Test
	public void validateMigrationAssetModel() throws Exception
	{
		String filePath = "C:/Users/lala/Desktop/Data Migration/ASSET_EQUIPMENT_CTPL.xls";
		Workbook workbook = Workbook.getWorkbook(new File(filePath));
		
		Sheet[] sheets = workbook.getSheets();
		
		SearchCriteria searchCriteria = new SearchCriteria();
		searchCriteria.addSearchCriteria("assetType", AssetModel.ASSETTYPE_EQUIPMENT);
		//searchCriteria.addSearchCriteria("assetType", AssetModel.ASSETTYPE_COMPONENT);
		List<AssetModel> assetModels = assetModelService.findAssetModels(searchCriteria);
		
		String assetModelCodeNotFound = null;
		for(int i=0;i<1;i++)
		{
			Sheet sheet= workbook.getSheet(i);
			
			int colLength = sheet.getColumns();
			int rowLength = sheet.getRows();
			String colName[] = new String[colLength];
			String colNameQuote[] = new String[colLength];
			
			for(int j=0;j<colLength;j++)
			{
				colName[j]=sheet.getCell(j,0).getContents();
				colNameQuote[j]=sheet.getCell(j,1).getContents();
			}
			
			String rowValue[][] = new String[rowLength][colLength];
			
			for(int j=1;j<rowLength;j++)
			{
				
				
				for(int k=0;k<colLength;k++)
				{
					
					rowValue[j][k]=sheet.getCell(k,j).getContents();
					
					
					//System.out.println(j+" "+rowValue[j][k]);
				}
				

				String assetModelCode = rowValue[j][2];
				if(StringUtils.isEmpty(assetModelCode)) continue;
				boolean found=false;
				for(int k=0;k<assetModels.size();k++)
				{
					AssetModel tmp = assetModels.get(k);
					if(StringUtils.equalsIgnoreCase(tmp.getAssetModelCode(),StringUtils.trim(assetModelCode)))
					{
						rowValue[j][2] = String.valueOf(tmp.getAssetModelId());
						found=true;
					}
				}
				
				if(!found)
				{
					if(!StringUtils.equalsIgnoreCase(assetModelCode, assetModelCodeNotFound))
					{
						assetModelCodeNotFound = assetModelCode; 
						System.out.println(assetModelCode +" not found");
						continue;
					}
				}
				
				//fw.write(sqlInsert+"("+StringUtils.join(rowValue[j],",")+");\r\n");		
			}
		}
		//fw.close();
		workbook.close();
	}
	
	@Test
	public void createProductionMigrationComponentTransaction() throws Exception
	{
		initializeAssetComponents();
		
		SearchCriteria searchCriteria = new SearchCriteria();
		searchCriteria.addSearchCriteria("assetType", AssetModel.ASSETTYPE_COMPONENT);
		
		Iterator<AssetModel> i = assetModels.iterator();
		while(i.hasNext())
		{
			AssetModel assetModel = i.next();
			List<AssetComponent> assetComponents = findAssetComponents(assetModel.getAssetModelCode());
			
			if(assetComponents!=null)
			{
				AssetTransaction assetTransaction = new AssetTransaction();
				assetTransaction.setDateCreated(new Date());
				assetTransaction.setCreatedBy("data-migration");
				assetTransaction.setAssetType(AssetModel.ASSETTYPE_COMPONENT);
				
				List<AssetTransactionItem> assetTransactionItems = new ArrayList<AssetTransactionItem>();
				
				for(int j=0;j<assetComponents.size();j++)
				{
					AssetComponent assetComponent = assetComponents.get(j);
					
					AssetTransactionItem assetTransactionItem = new AssetTransactionItem();
					assetTransactionItem.setAssetModel(assetComponent.getAssetModel());
					assetTransactionItem.setAssetTransactionQuantity(assetComponent.getQuantity());
					assetTransactionItem.setCompanyCode(assetComponent.getCompanyCode());
					assetTransactionItem.setLocation(assetComponent.getLocation());
					assetTransactionItem.setAssetPrice(assetComponent.getAssetPrice());
					//assetTransactionItem.setLocation(assetComponent.getp);
					assetTransactionItems.add(assetTransactionItem);
				}
				assetTransaction.setAssetTransactionItems(assetTransactionItems);
				assetTransactionService.createInitialBalance(assetTransaction);
			}
		}
		
		
	}
	
	private void initializeAssetComponents() throws BiffException, IOException
	{
		String filePath = "C:/Users/lala/Desktop/Data Migration/ASSET_COMPONENT.xls";
		Workbook workbook = Workbook.getWorkbook(new File(filePath));
		Sheet[] sheets = workbook.getSheets();
		
		SearchCriteria searchCriteria = new SearchCriteria();
		searchCriteria.addSearchCriteria("assetType", AssetModel.ASSETTYPE_COMPONENT);
		List<AssetModel> tmpAssetModels = assetModelService.findAssetModels(searchCriteria);
		
		
		searchCriteria = new SearchCriteria();
		List<Location> locations = locationService.findLocations(searchCriteria);
		
		for(int i=0;i<1;i++)
		{
			Sheet sheet= workbook.getSheet(i);
			
			int colLength = sheet.getColumns();
			int rowLength = sheet.getRows();
			String colName[] = new String[colLength];
			
			
			for(int j=0;j<colLength;j++)
			{
				colName[j]=sheet.getCell(j,0).getContents();
			}
			
			
			
			String rowValue[][] = new String[rowLength][colLength];
			
			for(int j=1;j<rowLength;j++)
			{
				for(int k=0;k<colLength;k++)
				{
					rowValue[j][k]=sheet.getCell(k,j).getContents();
					//System.out.println(j+" "+rowValue[j][k]);
				}
				

				String assetModelCode = rowValue[j][2];
				//System.out.println(j+ " "+assetModelCode);
				if(StringUtils.isEmpty(assetModelCode)) continue;
				boolean found=false;
				AssetModel assetModel = null;
				for(int k=0;k<tmpAssetModels.size();k++)
				{
					AssetModel tmp = tmpAssetModels.get(k);
					if(StringUtils.equalsIgnoreCase(tmp.getAssetModelCode(),StringUtils.trim(assetModelCode)))
					{
						assetModel = tmp;
						assetModels.add(assetModel);
						rowValue[j][2] = String.valueOf(tmp.getAssetModelId());
						found=true;
					}
				}
				
				if(!found)
				{
					System.out.println("Can't find "+assetModelCode);
					continue;
				}
				AssetComponent assetComponent = new AssetComponent();
				assetComponent.setCompanyCode(rowValue[j][0]);
				Location location = findLocation(locations, rowValue[j][1]);
				
				assetComponent.setLocation(location);
				
				assetComponent.setAssetModel(assetModel);
				assetComponent.setQuantity(Integer.valueOf(rowValue[j][3]));
				assetComponent.setAssetPrice(Float.valueOf(rowValue[j][4]));
				
				assetComponents.add(assetComponent);
			}
		}
		workbook.close();
	}
	
	private void initializeAssetEquipments() throws BiffException, IOException
	{
		String filePath = "C:/Users/lala/Desktop/Data Migration/ASSET_EQUIPMENT_CTPL.xls";
		Workbook workbook = Workbook.getWorkbook(new File(filePath));
		Sheet[] sheets = workbook.getSheets();
		
		SearchCriteria searchCriteria = new SearchCriteria();
		searchCriteria.addSearchCriteria("assetType", AssetModel.ASSETTYPE_EQUIPMENT);
		List<AssetModel> tmpAssetModels = assetModelService.findAssetModels(searchCriteria);
		
		
		searchCriteria = new SearchCriteria();
		List<Location> locations = locationService.findLocations(searchCriteria);
		Set<String> serialNoSet = new HashSet<String>();
		for(int i=0;i<1;i++)
		{
			Sheet sheet= workbook.getSheet(i);
			
			int colLength = sheet.getColumns();
			int rowLength = sheet.getRows();
			String colName[] = new String[colLength];
			
			
			for(int j=0;j<colLength;j++)
			{
				colName[j]=sheet.getCell(j,0).getContents();
			}
			
			
			
			String rowValue[][] = new String[rowLength][colLength];
			
			for(int j=1;j<rowLength;j++)
			{
				for(int k=0;k<colLength;k++)
				{
					rowValue[j][k]=sheet.getCell(k,j).getContents();
					//System.out.println(j+" "+rowValue[j][k]);
				}
				

				String assetModelCode = rowValue[j][2];
				//System.out.println(j+ " "+assetModelCode);
				if(StringUtils.isEmpty(assetModelCode)) continue;
				boolean found=false;
				AssetModel assetModel = null;
				for(int k=0;k<tmpAssetModels.size();k++)
				{
					AssetModel tmp = tmpAssetModels.get(k);
					if(StringUtils.equalsIgnoreCase(tmp.getAssetModelCode(),StringUtils.trim(assetModelCode)))
					{
						assetModel = tmp;
						assetModels.add(assetModel);
						rowValue[j][2] = String.valueOf(tmp.getAssetModelId());
						found=true;
					}
				}
				
				if(!found)
				{
					System.out.println("Can't find "+assetModelCode);
					continue;
				}
				AssetEquipment assetEquipment= new AssetEquipment();
				assetEquipment.setCompanyCode(rowValue[j][0]);
				Location location = findLocation(locations, rowValue[j][1]);
				
				assetEquipment.setLocation(location);
				if(serialNoSet.contains(rowValue[j][3]))
				{
					
					String tmp = rowValue[j][3];
					int ix=1;
					do {
						tmp=rowValue[j][3]+"-D"+ix;
						ix++;
					}while(serialNoSet.contains(tmp));
					
					System.out.println(j+": "+assetModelCode+" - "+rowValue[j][3] +" to "+tmp);
					rowValue[j][3]=tmp;
					
				}
				serialNoSet.add(rowValue[j][3]);
				AssetEquipmentPK assetEquipmentPK = new AssetEquipmentPK(assetModel, rowValue[j][3]);
				assetEquipment.setAssetEquipmentPK(assetEquipmentPK);
				assetEquipment.setVersion(0);
				
				
				assetEquipments.add(assetEquipment);
			}
		}
		workbook.close();
	}
	
	private List<AssetEquipment> findAssetEquipments(String assetModelCode) {
		List<AssetEquipment> tmp = new ArrayList<AssetEquipment>();
		for(int i=0;i<assetEquipments.size();i++)
		{
			AssetEquipment assetEquipment = assetEquipments.get(i);
			AssetModel assetModel = assetEquipment.getAssetEquipmentPK().getAssetModel();
			if(StringUtils.equalsIgnoreCase(assetModel.getAssetModelCode(), assetModelCode))
			{
				tmp.add(assetEquipment);
			}
		}
		return tmp;
	}
	
	private List<AssetComponent> findAssetComponents(String assetModelCode) {
		List<AssetComponent> tmp = new ArrayList<AssetComponent>();
		for(int i=0;i<assetComponents.size();i++)
		{
			AssetComponent assetComponent = assetComponents.get(i);
			AssetModel assetModel = assetComponent.getAssetModel();
			if(StringUtils.equalsIgnoreCase(assetModel.getAssetModelCode(), assetModelCode))
			{
				tmp.add(assetComponent);
			}
		}
		return tmp;
	}

	@Test
	public void createProductionMigrationEquipmentTransaction() throws Exception
	{
		initializeAssetEquipments();
		SearchCriteria searchCriteria = new SearchCriteria();
		searchCriteria.addSearchCriteria("assetType", AssetModel.ASSETTYPE_EQUIPMENT);
		Iterator<AssetModel> i = assetModels.iterator();
		while(i.hasNext())
		{
			AssetModel assetModel = i.next();
			List<AssetEquipment> assetEquipments = findAssetEquipments(assetModel.getAssetModelCode());
			
			if(assetEquipments!=null)
			{
				AssetTransaction assetTransaction = new AssetTransaction();
				assetTransaction.setDateCreated(new Date());
				assetTransaction.setCreatedBy("data-migration");
				assetTransaction.setAssetType(AssetModel.ASSETTYPE_EQUIPMENT);
				
				List<AssetTransactionItem> assetTransactionItems = new ArrayList<AssetTransactionItem>();
				
				for(int j=0;j<assetEquipments.size();j++)
				{
					AssetEquipment assetEquipment = assetEquipments.get(j);
					
					AssetTransactionItem assetTransactionItem = new AssetTransactionItem();
					assetTransactionItem.setAssetModel(assetEquipment.getAssetEquipmentPK().getAssetModel());
					assetTransactionItem.setCompanyCode(assetEquipment.getCompanyCode());
					assetTransactionItem.setLocation(assetEquipment.getLocation());
					assetTransactionItem.setSerialNo(assetEquipment.getAssetEquipmentPK().getSerialNo());
					assetTransactionItems.add(assetTransactionItem);
				}
				assetTransaction.setAssetTransactionItems(assetTransactionItems);
				assetTransactionService.createInitialBalance(assetTransaction);
			}
		}
	}
	
	@Test
	public void createProductionMigrationComponent() throws Exception
	{
		String filePath = "C:/Users/lala/Desktop/Data Migration/ASSET_COMPONENT.xls";
		Workbook workbook = Workbook.getWorkbook(new File(filePath));
		FileWriter fw = new FileWriter("data-migration/migration-asset-component.sql");
		fw.write("set define off;\r\n");
		Sheet[] sheets = workbook.getSheets();
		
		SearchCriteria searchCriteria = new SearchCriteria();
		searchCriteria.addSearchCriteria("assetType", AssetModel.ASSETTYPE_COMPONENT);
		List<AssetModel> assetModels = assetModelService.findAssetModels(searchCriteria);
		
		for(int i=0;i<1;i++)
		{
			String tableName = "AMTB_ASSET_COMPONENT";
			System.out.println(tableName);
			Sheet sheet= workbook.getSheet(i);
			
			int colLength = sheet.getColumns();
			int rowLength = sheet.getRows();
			String colName[] = new String[colLength];
			String colNameQuote[] = new String[colLength];
			
			
			
			for(int j=0;j<colLength;j++)
			{
				colName[j]=sheet.getCell(j,0).getContents();
				colNameQuote[j]=sheet.getCell(j,1).getContents();
			}
			
			
			
			String rowValue[][] = new String[rowLength][colLength];
			
			String sqlInsert = "INSERT INTO "+tableName+"("+StringUtils.join(colName, ",")+") values ";
			for(int j=2;j<rowLength;j++)
			{
				
				
				for(int k=0;k<colLength;k++)
				{
					
					rowValue[j][k]=sheet.getCell(k,j).getContents();
					
					if(StringUtils.equalsIgnoreCase(colNameQuote[k], "Y"))
						rowValue[j][k]="'"+StringEscapeUtils.escapeSql(rowValue[j][k])+"'";
					//System.out.println(j+" "+rowValue[j][k]);
				}
				

				String assetModelCode = rowValue[j][2];
				boolean found=false;
				for(int k=0;k<assetModels.size();k++)
				{
					AssetModel tmp = assetModels.get(k);
					if(StringUtils.equalsIgnoreCase(tmp.getAssetModelCode(),StringUtils.trim(assetModelCode)))
					{
						rowValue[j][2] = String.valueOf(tmp.getAssetModelId());
						found=true;
					}
				}
				
				if(!found)
				{
					fw.write("-- " + assetModelCode +" not found\r\n");
					continue;
				}
				
				fw.write(sqlInsert+"("+StringUtils.join(rowValue[j],",")+");\r\n");		
			}
		}
		fw.close();
		workbook.close();
		
	}
	
	@Test
	public void createProductionMigrationEquipment() throws Exception
	{
		String filePath = "C:/Users/lala/Desktop/Data Migration/ASSET_EQUIPMENT_CCPL.xls";
		Workbook workbook = Workbook.getWorkbook(new File(filePath));
		FileWriter fw = new FileWriter("data-migration/migration-asset-equipment_ccpl.sql");
		fw.write("set define off;\r\n");
		Sheet[] sheets = workbook.getSheets();
		
		SearchCriteria searchCriteria = new SearchCriteria();
		searchCriteria.addSearchCriteria("assetType", AssetModel.ASSETTYPE_EQUIPMENT);
		List<AssetModel> assetModels = assetModelService.findAssetModels(searchCriteria);
		
		for(int i=0;i<1;i++)
		{
			String tableName = "AMTB_ASSET_EQUIPMENT";
			System.out.println(tableName);
			Sheet sheet= workbook.getSheet(i);
			
			int colLength = sheet.getColumns();
			int rowLength = sheet.getRows();
			String colName[] = new String[colLength];
			String colNameQuote[] = new String[colLength];
			
			
			
			for(int j=0;j<colLength;j++)
			{
				colName[j]=sheet.getCell(j,0).getContents();
				colNameQuote[j]=sheet.getCell(j,1).getContents();
			}
			
			
			
			String rowValue[][] = new String[rowLength][colLength];
			
			String sqlInsert = "INSERT INTO "+tableName+"("+StringUtils.join(colName, ",")+") values ";
			
			int counterTag = 1;
			for(int j=2;j<rowLength;j++)
			{
				
				
				for(int k=0;k<colLength;k++)
				{
					
					rowValue[j][k]=sheet.getCell(k,j).getContents();
					
					if(StringUtils.equalsIgnoreCase(colNameQuote[k], "Y"))
						rowValue[j][k]="'"+StringEscapeUtils.escapeSql(rowValue[j][k])+"'";
					//System.out.println(j+" "+rowValue[j][k]);
				}
				

				String assetModelCode = rowValue[j][2];
				boolean found=false;
				for(int k=0;k<assetModels.size();k++)
				{
					AssetModel tmp = assetModels.get(k);
					if(StringUtils.equalsIgnoreCase(tmp.getAssetModelCode(),StringUtils.trim(assetModelCode)))
					{
						rowValue[j][2] = String.valueOf(tmp.getAssetModelId());
						found=true;
					}
				}
				
				
				

				if(!found)
				{
					fw.write("-- " + assetModelCode +" not found\r\n");
					continue;
				}
				
				String assetTag ="FA-"+"E-"+assetModelCode+"-"+new SimpleDateFormat("yyyyMMdd").format(new Date())+"-";
				rowValue[j][5] = "'"+assetTag+String.format("%05d", counterTag)+"'";
				counterTag++;
				
				fw.write(sqlInsert+"("+StringUtils.join(rowValue[j],",")+");\r\n");		
			}
		}
		fw.close();
		workbook.close();
		
	}
	
	public Location findLocation(List<Location> list,String locationCode)
	{
		
		if(list==null) return null;
		
		for(int i=0;i<list.size();i++)
		{
			Location location = (Location) list.get(i);
			if(StringUtils.equalsIgnoreCase(location.getLocationCode(), locationCode))
				return location;
		}
		
		return null;
	}
	
	@Test
	public void createScriptFromExcel() throws Exception
	{
		Workbook workbook = Workbook.getWorkbook(new File("c:/Book1.xls"));
		FileWriter fw = new FileWriter("c:/migration.sql");
		fw.write("set define off;\r\n");
		Sheet[] sheets = workbook.getSheets();
		
			
			Sheet sheet= workbook.getSheet(0);
			
			int colLength = sheet.getColumns();
			int rowLength = sheet.getRows();
			
			String rowValue[][] = new String[rowLength][colLength];
			
			
			for(int j=2;j<rowLength;j++)
			{
			
				String serialNo =sheet.getCell(0,j).getContents();
				String assetModelId =sheet.getCell(1,j).getContents();
				//System.out.println(serialNo);
				//fw.write(sqlInsert+"("+StringUtils.join(rowValue[j],",")+");\r\n");
				SearchCriteria searchCriteria = new SearchCriteria();
				searchCriteria.addSearchCriteria("serialNo", serialNo);
				searchCriteria.addSearchCriteria("assetModelId", Integer.valueOf(assetModelId));
				List<AssetEquipment> assetEquipments = assetModelService.findAssetEquipments(searchCriteria);
				if(assetEquipments==null || assetEquipments.size()==0)
				{
					System.out.println("serial no is not in list"+serialNo);
					continue;
				}
				boolean isInServiceCentre = false;
				
				String exactSerialNo=null;
				for(int k=0;k<assetEquipments.size();k++)
				{
					AssetEquipment assetEquipment = assetEquipments.get(k);
					exactSerialNo=assetEquipment.getAssetEquipmentPK().getSerialNo();
					if(assetEquipment.getLocation().getLocationId()==16)
					{
						isInServiceCentre = true;
						break;
					}
				}
				
				if(!isInServiceCentre)
				{
					
					System.out.println("update AIMSSYS.amtb_asset_equipment set locationid=16,sublocationid=null WHERE ASSETMODELID="+assetModelId+" AND SERIALNO='"+exactSerialNo+"';");
					//System.out.println("serial no is not in service centre "+serialNo+" "+assetEquipments.size());
					//System.out.println("serial no is not in service centre"+serialNo);
				}
			}
			
			//fw.write(sqlInsert+"("+StringUtils.join(rowValue[j],",")+");\r\n");
		fw.close();
		workbook.close();
	}
}
